package com.hongguowei.jpajpql;

import com.hongguowei.entity.Author;

import javax.persistence.*;
import javax.persistence.criteria.AbstractQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import java.util.List;

public class JPQL {
    public static void main(String[] args) {
        //创建实体管理器工厂
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Student_details");
        //获取实体管理器
        EntityManager em = emf.createEntityManager();
        //启动事务
        em.getTransaction().begin();
        /**
         * 利用标准API创建标准查询
         */
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Author> cq = cb.createQuery(Author.class);
        AbstractQuery<Author> cq1 = cb.createQuery(Author.class);
        AbstractQuery<Author> cq2= cb.createQuery(Author.class);
        CriteriaQuery<Object[]> cq3 = cb.createQuery(Object[].class);

        Root<Author> from = cq.from(Author.class);
        //单列查询
        cq.select(from.get("name"));
        //多列查询
        cq.multiselect(from.get("id"),from.get("name"));
        //升序排序
        cq.orderBy(cb.asc(from.get("age")));
        //降序排序
        cq.orderBy(cb.desc(from.get("age")));
        //where条件后的大于小于
        cq1.where(cb.greaterThan(from.get("age"), 22));
        cq2.where(cb.lessThan(from.get("age"), 22));
        CriteriaQuery<Author> select1 = ((CriteriaQuery<Author>) cq1).select(from);
        TypedQuery<Author> tq1 = em.createQuery(select1);
        //between
        cq1.where(cb.between(from.get("age"), 22, 26));
        //like
        cq1.where(cb.like(from.get("name"), "R%"));
        //in
        cq1.where(cb.in(from.get("age")).value(22).value(24));
        //GroupBy
        cq3.multiselect(from.get("age"), cb.count(from)).groupBy(from.get("age"));
        //Having
        cq3.multiselect(from.get("s_age"), cb.count(from)).groupBy(from.get("s_age"))
                .having(cb.ge(from.get("s_age"), 24));

        CriteriaQuery<Author> select = cq.select(from);
        TypedQuery<Author> q = em.createQuery(select);
        List<Author> list2 = q.getResultList();
        //查询
        Query query = em.createQuery("Select id from  tb_user_info ");
        //更新
        Query query1 = em.createQuery("update tb_user_info SET s_age=25 where s_id>103");
        query1.executeUpdate();
        //删除
        Query query3 = em.createQuery("delete from tb_user_info where s_id=102");
        query3.executeUpdate();

        @SuppressWarnings("unchecked")
        List<Author> list = query.getResultList();
        for (Author author : list) {
            System.out.println(author.getName());
        }
        //提交事务
        em.getTransaction().commit();
        //关闭资源
        em.close();
        emf.close();

    }


}




